﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SMALLERP.ComClass;
using SMALLERP.DataClass;
using System.Data.SqlClient;
using XJLSC.QM;
using MSword = Microsoft.Office.Interop.Excel;
using System.IO;
using XJLSC.PM;
using DevExpress.XtraEditors;
using System.Data.OleDb;

namespace SMALLERP.PM
{
    public partial class LLYC : Form
    {
        DataBase db = new DataBase();
        private SqlDataReader sdr1;
       CHAXUN f;
        public LLYC()
        {
            InitializeComponent();
        }

        public delegate void CheckBoxClickedHandler(bool state);
        public class DataGridViewCheckBoxHeaderCellEventArgs : EventArgs
        {
            bool _bChecked;
            public DataGridViewCheckBoxHeaderCellEventArgs(bool bChecked)
            {
                _bChecked = bChecked;
            }
            public bool Checked
            {
                get { return _bChecked; }
            }
        }
        class DatagridViewCheckBoxHeaderCell : DataGridViewColumnHeaderCell
        {
            Point checkBoxLocation;
            Size checkBoxSize;
            bool _checked = false;
            Point _cellLocation = new Point();
            System.Windows.Forms.VisualStyles.CheckBoxState _cbState =
                System.Windows.Forms.VisualStyles.CheckBoxState.UncheckedNormal;
            public event CheckBoxClickedHandler OnCheckBoxClicked;

            public DatagridViewCheckBoxHeaderCell()
            {
            }

            protected override void Paint(System.Drawing.Graphics graphics,
                System.Drawing.Rectangle clipBounds,
                System.Drawing.Rectangle cellBounds,
                int rowIndex,
                DataGridViewElementStates dataGridViewElementState,
                object value,
                object formattedValue,
                string errorText,
                DataGridViewCellStyle cellStyle,
                DataGridViewAdvancedBorderStyle advancedBorderStyle,
                DataGridViewPaintParts paintParts)
            {
                base.Paint(graphics, clipBounds, cellBounds, rowIndex,
                    dataGridViewElementState, value,
                    formattedValue, errorText, cellStyle,
                    advancedBorderStyle, paintParts);
                Point p = new Point();
                Size s = CheckBoxRenderer.GetGlyphSize(graphics,
                System.Windows.Forms.VisualStyles.CheckBoxState.UncheckedNormal);
                p.X = cellBounds.Location.X +
                    (cellBounds.Width / 2) - (s.Width / 2);
                p.Y = cellBounds.Location.Y +
                    (cellBounds.Height / 2) - (s.Height / 2);
                _cellLocation = cellBounds.Location;
                checkBoxLocation = p;
                checkBoxSize = s;
                if (_checked)
                    _cbState = System.Windows.Forms.VisualStyles.
                        CheckBoxState.CheckedNormal;
                else
                    _cbState = System.Windows.Forms.VisualStyles.
                        CheckBoxState.UncheckedNormal;
                CheckBoxRenderer.DrawCheckBox
                (graphics, checkBoxLocation, _cbState);
            }

            protected override void OnMouseClick(DataGridViewCellMouseEventArgs e)
            {
                Point p = new Point(e.X + _cellLocation.X, e.Y + _cellLocation.Y);
                if (p.X >= checkBoxLocation.X && p.X <=
                    checkBoxLocation.X + checkBoxSize.Width
                && p.Y >= checkBoxLocation.Y && p.Y <=
                    checkBoxLocation.Y + checkBoxSize.Height)
                {
                    _checked = !_checked;
                    if (OnCheckBoxClicked != null)
                    {
                        OnCheckBoxClicked(_checked);
                        this.DataGridView.InvalidateCell(this);
                    }

                }
                base.OnMouseClick(e);
            }
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
           
        }

        private void CJBGD_Load(object sender, EventArgs e)
        {
            try
            {
                textBox9.Tag = "";
                textBox2.Text = "5601";
                textBox1.Text = "5690";
                textBox3.Text = "0.00";
                textBox4.Text = "0.00";
                //textBox5.Text = "000";
                //DataGridViewCheckBoxColumn colCB = new DataGridViewCheckBoxColumn();
                //DatagridViewCheckBoxHeaderCell bbHeader = new DatagridViewCheckBoxHeaderCell();
                //colCB.HeaderCell = bbHeader;
                //colCB.Name = "colCB";
                //colCB.Width = 20;
                //colCB.HeaderText = "";
                //gridControl1.Columns.Insert(0, colCB);
                //bbHeader.OnCheckBoxClicked +=
                //    new CheckBoxClickedHandler(abHeader_OnCheckBoxClicked);

                DataGridViewCheckBoxColumn colCC = new DataGridViewCheckBoxColumn();
                DatagridViewCheckBoxHeaderCell cbHeader = new DatagridViewCheckBoxHeaderCell();
                colCC.HeaderCell = cbHeader;
                colCC.Name = "colCC";
                colCC.Width = 20;
                colCC.HeaderText = "";
                dataGridView2.Columns.Insert(0, colCC);
                cbHeader.OnCheckBoxClicked +=
                    new CheckBoxClickedHandler(cbHeader_OnCheckBoxClicked);
            }
            catch
            {

            }
        }

        private void abHeader_OnCheckBoxClicked(bool state)
        {
            //这一句很重要结束编辑状态
            //gridView1.EndEdit();
            //gridView1.Rows.OfType<DataGridViewRow>().ToList().ForEach(t => t.Cells[0].Value = state);
        }

        private void cbHeader_OnCheckBoxClicked(bool state)
        {
            //这一句很重要结束编辑状态
            dataGridView2.EndEdit();
            dataGridView2.Rows.OfType<DataGridViewRow>().ToList().ForEach(t => t.Cells[0].Value = state);
        }
        private void button1_Click(object sender, EventArgs e)
        {
            String BL = "0.00";     //超领比例
            String DB = "";         //领退料单别
            String CK = "";         // 仓库
            String GC = "";         // 工厂
            String SYGDLJ="0";       // 非工单料件 0.显示需领用量为0,1.不显示需领用量为0 
            String XZ = "";         //性质1.厂内,2.委外

           if (comboBox1.Text=="厂内")
            {
                XZ = "1";

            }
            else
            {
                XZ = "2";
            }

            if (comboBox2.Text == "百琪达")
            {
                GC = "90DDCEE1-93C2-432E-F0A5-1712B6B24FB6";
                if (comboBox1.Text == "厂内")
                {
                    textBox1.Text = "5640";
                }
                else
                {
                    textBox1.Text = "5630";
                }
               
              
            }
            else
            {
                GC = "8E9AE49D-0DEB-452D-019E-192E7CBBF1E5";
                if (comboBox1.Text == "厂内")
                {
                    textBox1.Text = "5680";
                }
                else
                {
                    textBox1.Text = "5681";
                }
            }

            String HB = "0";          //合并产生单据(相同工作中心或者供应商的工单合并)0.合并,1.不合并
            String strSql = "";

            if (radioButton1.Checked == true)
            {
                strSql = "SELECT DOC_ID FROM DOC WHERE DOC_CODE='" + textBox2.Text + "'";
                BL = textBox4.Text;
            }
            else
            {
                strSql = "SELECT DOC_ID FROM DOC WHERE DOC_CODE='" + textBox1.Text + "'";
                BL = textBox3.Text;
            }
          
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    DB = sdr1["DOC_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }
           
            strSql = "SELECT WAREHOUSE_ID FROM WAREHOUSE WHERE WAREHOUSE_CODE='000'";
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    CK = sdr1["WAREHOUSE_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }
          
            if (radioButton1.Checked == true)
            {
                if(checkBox1.CheckState==CheckState.Unchecked)
                {
                    if (comboBox1.Text == "厂内")
                    {
                        strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.MO_D_ID=G.MO_D_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,SUM(ACCEPTED_QTY+DESTROYED_QTY+SCRAP_QTY) ACCEPTED_QTY FROM MO_RECEIPT_D LEFT JOIN MO_RECEIPT ON MO_RECEIPT_D.MO_RECEIPT_ID=MO_RECEIPT.MO_RECEIPT_ID WHERE MO_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID) H ON A.MO_ID=H.MO_ID ";
                        strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))<(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00*(-1.00)-1) AND A.Owner_Org_ROid='" + GC + "' AND A.ACTUAL_COMPLETE_DATE>='" + dateTimePicker1.Value.ToShortDateString() + "' AND A.ACTUAL_COMPLETE_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                    }
                    else
                    {
                        strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.MO_D_ID=G.MO_D_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                        strSql += " LEFT JOIN (SELECT REFERENCE_SOURCE_ID_ROid AS MO_ID,SUM(RECEIPTED_BUSINESS_QTY) ACCEPTED_QTY FROM PURCHASE_ARRIVAL_D LEFT JOIN PURCHASE_ARRIVAL ON PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID=PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID WHERE REFERENCE_SOURCE_ID_RTK='MO' AND PURCHASE_ARRIVAL.ARRIVAL_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY REFERENCE_SOURCE_ID_ROid) H ON A.MO_ID=H.MO_ID ";//A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                        strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))<(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00*(-1.00)-1) AND A.Owner_Org_ROid='" + GC + "' AND A.ACTUAL_COMPLETE_DATE>='" + dateTimePicker1.Value.ToShortDateString() + "' AND A.ACTUAL_COMPLETE_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                    }
                }
                else
                {
                    if (comboBox1.Text == "厂内")
                    {
                        strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.MO_D_ID=G.MO_D_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,SUM(ACCEPTED_QTY+DESTROYED_QTY+SCRAP_QTY) ACCEPTED_QTY FROM MO_RECEIPT_D LEFT JOIN MO_RECEIPT ON MO_RECEIPT_D.MO_RECEIPT_ID=MO_RECEIPT.MO_RECEIPT_ID WHERE MO_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID) H ON A.MO_ID=H.MO_ID ";
                        strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))<(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00*(-1.00)-1) AND A.Owner_Org_ROid='" + GC + "' AND ((A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]<>'Y' AND A.[STATUS]<>'y')) OR (A.[STATUS]='Y' OR A.[STATUS]='y'))";
                    }
                    else
                    {
                        strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                        strSql += " LEFT JOIN (SELECT MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ISSUE_RECEIPT_D.MO_D_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.MO_D_ID=G.MO_D_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                        strSql += " LEFT JOIN (SELECT REFERENCE_SOURCE_ID_ROid AS MO_ID,SUM(RECEIPTED_BUSINESS_QTY) ACCEPTED_QTY FROM PURCHASE_ARRIVAL_D LEFT JOIN PURCHASE_ARRIVAL ON PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID=PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID WHERE REFERENCE_SOURCE_ID_RTK='MO' AND PURCHASE_ARRIVAL.ARRIVAL_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY REFERENCE_SOURCE_ID_ROid) H ON A.MO_ID=H.MO_ID ";//A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                        strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))<(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00*(-1.00)-1) AND A.Owner_Org_ROid='" + GC + "' AND ((A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')) OR (A.[STATUS]<>'Y' AND A.[STATUS]<>'y'))";
                    }
                }

            }
            else
            {
                if (comboBox1.Text == "厂内")
                {
                    strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                    strSql += " LEFT JOIN (SELECT MO_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                    strSql += " LEFT JOIN (SELECT MO_ID,SUM(ACCEPTED_QTY+DESTROYED_QTY+SCRAP_QTY) ACCEPTED_QTY FROM MO_RECEIPT_D LEFT JOIN MO_RECEIPT ON MO_RECEIPT_D.MO_RECEIPT_ID=MO_RECEIPT.MO_RECEIPT_ID WHERE MO_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID) H ON A.MO_ID=H.MO_ID ";
                    strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))>B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00 AND A.Owner_Org_ROid='" + GC + "' AND ((A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker1.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')) OR (A.[STATUS]<>'Y' AND A.[STATUS]<>'y'))"; // A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                }
                else
                {
                    strSql = "SELECT DOC_NO 工单号,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.STATUS 工单状态,CAST(A.PLAN_QTY AS REAL) 预计产量,CAST(ISNULL(H.ACCEPTED_QTY,0) AS REAL) 已生产量,CAST(SCRAP_QTY AS REAL) 报废数量,CAST(DESTROYED_QTY AS REAL) 破坏数量,E.ITEM_CODE 产品品号,A.ITEM_DESCRIPTION 产品品名,A.ITEM_SPECIFICATION 产品规格,SOURCE_ID_RTK AS SOURCEIDRTK,SOURCE_ID_ROid AS SOURCEIDROid,ISNULL(C.WORK_CENTER_NAME,'')+ISNULL(D.SUPPLIER_NAME,'')来源单位,F.ITEM_CODE 材料品号,B.ITEM_DESCRIPTION 材料品名,B.ITEM_SPECIFICATION 材料规格,ROUND(CAST(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)) AS REAL),0) 预计用量,CAST(ISNULL(G.ISSUE_RECEIPT_QTY,0) AS REAL) 已领用量,CAST(ROUND(ABS(B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0)),2) AS REAL) 领退料数量,A.Owner_Org_ROid AS OwnerOrgROid FROM MO A INNER JOIN MO_D B ON A.MO_ID = B.MO_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID ";
                    strSql += " LEFT JOIN (SELECT MO_ID,ITEM_ID,OPERATION_ID,SUM(ISSUE_RECEIPT_QTY*DOC.STOCK_ACTION*(-1.00)) ISSUE_RECEIPT_QTY FROM ISSUE_RECEIPT_D LEFT JOIN ISSUE_RECEIPT ON ISSUE_RECEIPT_D.ISSUE_RECEIPT_ID=ISSUE_RECEIPT.ISSUE_RECEIPT_ID LEFT JOIN DOC ON DOC.DOC_ID=ISSUE_RECEIPT.DOC_ID WHERE ISSUE_RECEIPT.TRANSACTION_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY MO_ID,ITEM_ID,OPERATION_ID) G ON B.MO_ID=G.MO_ID AND B.ITEM_ID=G.ITEM_ID AND B.OPERATION_ID=G.OPERATION_ID ";
                    strSql += " LEFT JOIN (SELECT REFERENCE_SOURCE_ID_ROid AS MO_ID,SUM(RECEIPTED_BUSINESS_QTY) ACCEPTED_QTY FROM PURCHASE_ARRIVAL_D LEFT JOIN PURCHASE_ARRIVAL ON PURCHASE_ARRIVAL_D.PURCHASE_ARRIVAL_ID=PURCHASE_ARRIVAL.PURCHASE_ARRIVAL_ID WHERE REFERENCE_SOURCE_ID_RTK='MO' AND PURCHASE_ARRIVAL.ARRIVAL_DATE<='" + dateTimePicker2.Value.ToShortDateString() + "' GROUP BY REFERENCE_SOURCE_ID_ROid) H ON A.MO_ID=H.MO_ID ";//A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker2.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')";
                    strSql += " WHERE ITEM_TYPE<>4 AND DOC_ID IN(SELECT DOC_ID FROM DOC WHERE PROCESS_TYPE = '" + XZ + "') AND (B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0)-ISNULL(G.ISSUE_RECEIPT_QTY,0))>B.REQUIRED_QTY/A.PLAN_QTY*ISNULL(H.ACCEPTED_QTY,0) * " + BL + "/100.00 AND A.Owner_Org_ROid='" + GC + "' AND ((A.ACTUAL_COMPLETE_DATE>'" + dateTimePicker1.Value.ToShortDateString() + "' AND (A.[STATUS]='Y' OR A.[STATUS]='y')) OR (A.[STATUS]<>'Y' AND A.[STATUS]<>'y'))"; 
                }
            }
           
             textBox6.Text = strSql;
            //return;
            try
            {
                DataTable dt;
                
                this.gridControl1.DataSource = "";
                gridControl1.Refresh();
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl1.DataSource = dt;
                gridView1.OptionsView.EnableAppearanceEvenRow = true;
                gridView1.OptionsView.EnableAppearanceOddRow= true;
                gridView1.Columns["MOID"].Visible = false;
                gridView1.Columns["MODID"].Visible = false;
                gridView1.Columns["OwnerOrgROid"].Visible = false;
                gridView1.Columns["SOURCEIDRTK"].Visible = false;
                gridView1.Columns["SOURCEIDROid"].Visible = false;
                //this.gridControl1.AllowUserToAddRows = false;
                //this.gridControl1.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;      
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            String strSql = "";
            String CK = "";
            String GC = "";
            strSql = "DELETE FROM MO_TEMP_LTL1 WHERE DLH='" + PropertyClass.OperatorCode + "'";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch
            {

            }

            if (comboBox2.Text == "百琪达")
            {
                GC = "90DDCEE1-93C2-432E-F0A5-1712B6B24FB6";
                if (comboBox1.Text == "厂内")
                {
                    textBox1.Text = "5640";
                    textBox2.Text = "5710";
                }
                else
                {
                    textBox1.Text = "5630";
                    textBox2.Text = "5720";
                }


            }
            else
            {
                GC = "8E9AE49D-0DEB-452D-019E-192E7CBBF1E5";
                if (comboBox1.Text == "厂内")
                {
                    textBox1.Text = "5680";
                    textBox2.Text = "5761";
                }
                else
                {
                    textBox1.Text = "5681";
                    textBox2.Text = "5754";
                }
            }

            strSql = "SELECT WAREHOUSE_ID FROM WAREHOUSE WHERE WAREHOUSE_CODE='000'";
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    CK = sdr1["WAREHOUSE_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            int[] rows = this.gridView1.GetSelectedRows();
            foreach (int row in rows)
            {
                    String mo_id = this.gridView1.GetRowCellValue(row, "MOID").ToString();
                    String mo_d_id = this.gridView1.GetRowCellValue(row, "MODID").ToString();
                    String source_rtk = this.gridView1.GetRowCellValue(row, "SOURCEIDRTK").ToString();
                    String source_roid = this.gridView1.GetRowCellValue(row, "SOURCEIDROid").ToString();
                    String qty = this.gridView1.GetRowCellValue(row, "领退料数量").ToString(); 
                    String owner = this.gridView1.GetRowCellValue(row, "OwnerOrgROid").ToString();
                    strSql = "INSERT MO_TEMP_LTL1 SELECT '" + PropertyClass.OperatorCode + "','" + mo_id + "','" + mo_d_id+ "','" + source_rtk + "','" + source_roid + "'," + qty + ",'','" + owner + "','" + CK + "',''";
                textBox6.Text = strSql;
               // return;
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                    }
                    else
                    {

                    }
                }
                catch
                {

                }
            }
            if (radioButton2.Checked == true)
            {
                strSql = "EXEC [SP_MO_TEMP_LTL1] '" + PropertyClass.OperatorCode + "','" + textBox1.Text + "','000','0','" + dateTimePicker2.Value.ToShortDateString() + "'";
                textBox6.Text = strSql;
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                        MessageBox.Show("领料单生成成功！", "软件提示");
                    }
                    else
                    {

                    }
                }
                catch
                {

                }
            }
            else
            {
                if (checkBox1.CheckState == CheckState.Checked)
                {
                    strSql = "EXEC [SP_MO_TEMP_LTL1] '" + PropertyClass.OperatorCode + "','" + textBox2.Text + "','000','0','" + dateTimePicker1.Value.AddMonths(0).ToShortDateString() + "'";
                }
                else
                {
                    strSql = "EXEC [SP_MO_TEMP_LTL1] '" + PropertyClass.OperatorCode + "','" + textBox2.Text + "','000','0','" + dateTimePicker2.Value.AddMonths(0).ToShortDateString() + "'";
                }
                textBox6.Text = strSql;
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                        MessageBox.Show("退料单生成成功！", "软件提示");
                    }
                    else
                    {

                    }
                }
                catch
                {

                }
            }

            if(checkBox1.CheckState==CheckState.Checked)
            {
                strSql = "EXEC [SP_MO_TEMP_LTL1] '" + PropertyClass.OperatorCode + "','" + textBox1.Text + "','000','0','" + dateTimePicker2.Value.AddMonths(0).ToShortDateString() + "'";
                textBox6.Text = strSql;
                try
                {
                    if (db.ExecDataBySql1(strSql) > 0)
                    {
                        MessageBox.Show("退料单生成成功！", "软件提示");
                    }
                    else
                    {

                    }
                }
                catch
                {

                }
            }
        }
        private void button4_Click(object sender, EventArgs e)
        {

        }
        private void button5_Click(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl2.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            SaveFileDialog fileDialog = new SaveFileDialog();
            fileDialog.Title = "导出Excel";
            fileDialog.Filter = "Excel文件(*.xls)|*.xls";
            DialogResult dialogResult = fileDialog.ShowDialog(this);
            if (dialogResult == DialogResult.OK)
            {
                DevExpress.XtraPrinting.XlsExportOptions options = new DevExpress.XtraPrinting.XlsExportOptions();
                gridControl1.ExportToXls(fileDialog.FileName);
                DevExpress.XtraEditors.XtraMessageBox.Show("保存成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void button4_Click_1(object sender, EventArgs e)
        {
            String strSql="";
            String plantid = "";
            if (textBox9.Text == "")
            {
                MessageBox.Show("工厂字段不能为空！", "软件提示");
                return;
            }
            //if (Convert.ToDouble(textBox16.Text) <= 0)
            //{
            //    MessageBox.Show("匹配金额必须大于0！", "软件提示");
            //    return;
            //}

            strSql = "DELETE FROM BOMMD";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch
            {

            }

            for (int i = 0; i <= gridView3.RowCount - 1; i++)
            {
                String yjph = this.gridView3.GetRowCellValue(i, "元件品号").ToString();
                String zjph = this.gridView3.GetRowCellValue(i, "主件品号").ToString();
                String tzbl = this.gridView3.GetRowCellValue(i, "调整比例").ToString();
                if (Convert.ToDouble(tzbl) > 0.00)
                {             
                    strSql = "INSERT INTO BOMMD SELECT '" + zjph + "','" + yjph + "'," + tzbl + "";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {
                        }
                    }
                    catch
                    {
                    }
                }
            }

            strSql = "SELECT PLANT_ID FROM PLANT WHERE PLANT_CODE='" + textBox9.Tag + "'";  //抓取单别id
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    plantid = sdr1["PLANT_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT I.DOC_NO 领料单号,H.SequenceNumber AS 领料序号,F.ITEM_CODE AS 品号,H.ITEM_DESCRIPTION 品名,H.ITEM_SPECIFICATION 规格,ISNULL(G.LOT_CODE,'') 批号,A.DOC_NO 工单单号,CAST(H.ISSUE_RECEIPT_QTY AS REAL) 领用数量,CAST(ISNULL(Z.COST_AMT,0) AS REAL) 领用金额,CAST(ISNULL(MD006,0) AS REAL) 调整比例,CAST(H.ISSUE_RECEIPT_QTY*MD006 AS REAL) AS 调整数量,CAST(ISNULL(Z.COST_AMT,0)*MD006 AS REAL) AS 调整金额,CAST(H.ISSUE_RECEIPT_QTY*(1-MD006) AS REAL) 调整后数量,CAST(ISNULL(Z.COST_AMT,0)*(1-MD006) AS REAL) 调整后金额,CAST(ISNULL(Z.UNIT_COST,0) AS REAL) 单位成本,H.WAREHOUSE_ID 仓库,A.MO_ID AS MOID,B.MO_D_ID AS MODID,A.SOURCE_ID_RTK AS SOURCEIDRTK,A.SOURCE_ID_ROid AS SOURCEIDROid,A.Owner_Org_ROid AS OwnerOrgROid,H.ISSUE_RECEIPT_D_ID FROM ISSUE_RECEIPT_D H LEFT JOIN ISSUE_RECEIPT I ON H.ISSUE_RECEIPT_ID=I.ISSUE_RECEIPT_ID LEFT JOIN MO A ON H.MO_ID=A.MO_ID INNER JOIN MO_D B ON A.MO_ID = B.MO_ID AND H.ITEM_ID=B.ITEM_ID LEFT JOIN dbo.WORK_CENTER C ON A.SOURCE_ID_ROid = C.WORK_CENTER_ID LEFT JOIN dbo.SUPPLIER D ON A.SOURCE_ID_ROid = D.SUPPLIER_BUSINESS_ID LEFT JOIN ITEM E ON A.ITEM_ID=E.ITEM_BUSINESS_ID LEFT JOIN ITEM F ON B.ITEM_ID=F.ITEM_BUSINESS_ID LEFT JOIN ITEM_LOT G ON H.ITEM_LOT_ID=G.ITEM_LOT_ID AND H.ITEM_ID=G.ITEM_ID LEFT JOIN TRANSACTION_LINE Z ON  I.DOC_NO=Z.DOC_NO AND H.SequenceNumber=Z.SEQUENCE_NO LEFT JOIN BOMMD ON E.ITEM_CODE=MD001 AND F.ITEM_CODE=MD003  ";
            strSql += " WHERE  I.ApproveStatus='Y' AND I.DOC_DATE BETWEEN CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker3.Value.ToShortDateString() + "'),112) AND CONVERT(CHAR(8),DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "'),112) AND I.Owner_Org_ROid='" + plantid + "' AND ISNULL(MD006,0)<>0";
   
            try
            {
                DataTable dt;                
                this.gridControl2.DataSource = "";
                gridControl2.Refresh();
                dt = db.GetDataSet1(strSql, "PURTD").Tables["PURTD"];
                gridControl2.DataSource = dt;
                gridView2.OptionsView.EnableAppearanceEvenRow = true;
                gridView2.OptionsView.EnableAppearanceOddRow= false;
                //gridView2.Columns["仓库"].Visible = false;
                //gridView2.Columns["MOID"].Visible = false;
                //gridView2.Columns["MODID"].Visible = false;
                //gridView2.Columns["OwnerOrgROid"].Visible = false;
                //gridView2.Columns["SOURCEIDRTK"].Visible = false;
                //gridView2.Columns["SOURCEIDROid"].Visible = false;
                //this.gridControl1.AllowUserToAddRows = false;
                //this.gridControl1.RowsDefaultCellStyle.WrapMode = DataGridViewTriState.True;
                //double tzje = 0.00;
                //double tzsl = 0.00;
                //double tzhje = 0.00;
                //double tzhsl = 0.00;

                //for (int i = 0; i <= gridView2.RowCount - 1; i++)
                //{
                //    if (Convert.ToDouble(gridView2.GetRowCellValue(i, "单位成本").ToString()) > 0.00)
                //    {
                        //tzje =Math.Round(Convert.ToDouble(gridView2.GetRowCellValue(i, "领用金额").ToString()) / Convert.ToDouble(gridView2.Columns["领用金额"].SummaryItem.SummaryValue.ToString()) * Convert.ToDouble(textBox16.Text),2);
                        //tzsl = Math.Round(Convert.ToDouble(gridView2.GetRowCellValue(i, "领用金额").ToString()) / Convert.ToDouble(gridView2.Columns["领用金额"].SummaryItem.SummaryValue.ToString()) * Convert.ToDouble(textBox16.Text) / Convert.ToDouble(gridView2.GetRowCellValue(i, "单位成本").ToString()),2);
                        //this.gridView2.GetDataRow(i)["调整数量"] = tzsl.ToString();
                        //this.gridView2.GetDataRow(i)["调整金额"] = tzje.ToString();
                        //tzhsl = Math.Round(Convert.ToDouble(gridView2.GetRowCellValue(i, "领用数量").ToString()) - Convert.ToDouble(gridView2.GetRowCellValue(i, "调整数量").ToString()),2);
                        //tzhje = Math.Round(Convert.ToDouble(gridView2.GetRowCellValue(i, "领用金额").ToString()) - Convert.ToDouble(gridView2.GetRowCellValue(i, "调整金额").ToString()),2);
                        //this.gridView2.GetDataRow(i)["调整后数量"] = tzhsl.ToString();
                        //this.gridView2.GetDataRow(i)["调整后金额"] = tzhje.ToString();
                //    }
                //}
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
        }

        private void button7_Click(object sender, EventArgs e)
        {
            string x1 = "";
            string x2 = "";
            double x3 = 0;
            string x4 = "";
            string x5 = "";
            string x6 = "";
            string x7 = "";
            x4 = "查询E10工厂";
            MXCX xx = new MXCX(x1, x2, x3, x4, x5);
            f = new CHAXUN();
            f.DataSelected += new EventHandler(f_DataSelected);
            f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
            f.Show(this);
        }

        void f_DataSelected(object sender, EventArgs e)
        {
            textBox9.Tag = f.clph;
            textBox9.Text = f.clpm;
        }

        private void textBox2_TextChanged(object sender, EventArgs e)
        {

        }

        private void button8_Click(object sender, EventArgs e)
        {
            try
            {
                string x1 = textBox9.Tag.ToString();
                string x2 = "";
                double x3 = 0;
                string x4 = "";
                string x5 = "";
                string x6 = "";
                string x7 = "";
                x4 = "查询E10工作中心";
                MXCX xx = new MXCX(x1, x2, x3, x4, x5);
                f = new CHAXUN();
                f.DataSelected += new EventHandler(f2_DataSelected);
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                f.Show(this);
            }
            catch
            {
                MessageBox.Show("请先选择工厂信息","系统提示");
            }
        }

        void f2_DataSelected(object sender, EventArgs e)
        {
            textBox10.Tag = f.clph;
            textBox10.Text = f.clpm;
        }

        private void button9_Click(object sender, EventArgs e)
        {
            try
            {
                string x1 = "";
                string x2 = "";
                double x3 = 0;
                string x4 = "";
                string x5 = "";
                string x6 = "";
                string x7 = "";
                x4 = "查询退料单别";
                MXCX xx = new MXCX(x1, x2, x3, x4, x5);
                f = new CHAXUN();
                f.DataSelected += new EventHandler(f3_DataSelected);
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                f.Show(this);
            }
            catch
            {
               
            }
        }

        void f3_DataSelected(object sender, EventArgs e)
        {
            textBox11.Tag = f.clph;
            textBox11.Text = f.clpm;
        }

        private void button10_Click(object sender, EventArgs e)
        {
            try
            {
                string x1 = "";
                string x2 = "";
                double x3 = 0;
                string x4 = "";
                string x5 = "";
                string x6 = "";
                string x7 = "";
                x4 = "查询费用领料单别";
                MXCX xx = new MXCX(x1, x2, x3, x4, x5);
                f = new CHAXUN();
                f.DataSelected += new EventHandler(f4_DataSelected);
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                f.Show(this);
            }
            catch
            {

            }
        }

        void f4_DataSelected(object sender, EventArgs e)
        {
            textBox14.Tag = f.clph;
            textBox14.Text = f.clpm;
        }

        private void button11_Click(object sender, EventArgs e)
        {
            try
            {
                string x1 = "";
                string x2 = "";
                double x3 = 0;
                string x4 = "";
                string x5 = "";
                string x6 = "";
                string x7 = "";
                x4 = "查询费用领料人员";
                MXCX xx = new MXCX(x1, x2, x3, x4, x5);
                f = new CHAXUN();
                f.DataSelected += new EventHandler(f5_DataSelected);
                f.cdgzzx += new CHAXUN.Cdgzzx(textBox2_TextChanged);
                f.Show(this);
            }
            catch
            {

            }
        }

        void f5_DataSelected(object sender, EventArgs e)
        {
            textBox15.Tag = f.clph;
            textBox15.Text = f.clpm;
        }

        private void button6_Click(object sender, EventArgs e)
        {
            String strSql = "";
            String DB = "";
            String CK = "";
            String LLRY = "";
            String plantid = "";
            if (textBox11.Text == "")
            {
                MessageBox.Show("退料单别不能为空！", "软件提示");
                return;
            }
            if (textBox14.Text=="")
            {
                MessageBox.Show("费用领料单别不能为空！", "软件提示");
                return;
            }
            //if (Convert.ToDouble(textBox16.Text)<=0)
            //{
            //    MessageBox.Show("匹配金额必须大于0！", "软件提示");
            //    return;
            //}

            strSql = "DELETE FROM MO_TEMP_LTL1 WHERE DLH='" + PropertyClass.OperatorCode + "'";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch
            {

            }

            strSql = "SELECT PLANT_ID FROM PLANT WHERE PLANT_CODE='" + textBox9.Tag + "'";  //抓取单别id
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    plantid = sdr1["PLANT_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT DOC_ID FROM DOC WHERE DOC_CODE='" + textBox14.Tag + "'";  //抓取单别id
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    DB = sdr1["DOC_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT EMPLOYEE_ID FROM EMPLOYEE WHERE EMPLOYEE_CODE='" + textBox15.Tag + "'";  //抓取领料人员id
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    LLRY = sdr1["EMPLOYEE_ID"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }
            String DH = "";

            strSql = "SELECT ISNULL(MAX(DOC_NO),'') DOC_NO FROM dbo.TRANSACTION_DOC WHERE DOC_ID='" + DB + "' AND DOC_DATE=CAST(GETDATE() AS DATE)";  //抓取单别id
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    DH = sdr1["DOC_NO"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }

            strSql = "SELECT CASE WHEN '" + DH + "'='' THEN  DOC_CODE+'-'+RIGHT(YEAR(GETDATE()),YEAR_DIGIT)+CASE WHEN  CODING_BY=1 THEN RIGHT(CONVERT(CHAR(8),GETDATE(),112),4) WHEN CODING_BY=2 THEN CAST(MONTH(GETDATE()) AS CHAR) ELSE '' END +RIGHT(RTRIM('0000'+CAST(RIGHT(ISNULL(MAX('" + DH + "'),0),SEQUENCE_DIGIT)+DENSE_RANK() OVER( ORDER BY TRANSACTION_DOC.Owner_Org_ROid,PLANT_ID ) AS CHAR)),SEQUENCE_DIGIT) ELSE LEFT(RTRIM('" + DH + "'),LEN('" + DH + "')-SEQUENCE_DIGIT)+RIGHT(RTRIM('0000'+CAST(RIGHT(RTRIM(ISNULL(MAX('" + DH + "'),0)),SEQUENCE_DIGIT)+DENSE_RANK() OVER( ORDER BY TRANSACTION_DOC.Owner_Org_ROid,PLANT_ID ) AS CHAR)),SEQUENCE_DIGIT) END DH  FROM DOC LEFT JOIN TRANSACTION_DOC ON 1=1  WHERE  DOC.DOC_CODE='" + textBox14.Tag + "' GROUP BY CODING_BY,YEAR_DIGIT,SEQUENCE_DIGIT,DOC_CODE,TRANSACTION_DOC.Owner_Org_ROid,PLANT_ID";  //抓取单号
            try
            {
                sdr1 = db.GetDataReader1(strSql);
                while (sdr1.Read())
                {
                    DH = sdr1["DH"].ToString();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "软件提示");
                throw ex;
            }
            finally
            {
                sdr1.Close();
            }
            String tzsl = "0.0";
            String tzje = "0.0";
            tzsl=gridView2.Columns["调整数量"].SummaryItem.SummaryValue.ToString();
            tzje=gridView2.Columns["调整金额"].SummaryItem.SummaryValue.ToString();
            //strSql = "INSERT INTO dbo.TRANSACTION_DOC(DOC_NO,DOC_DATE,DOC_ID,Owner_Dept,Owner_Emp,TRANSACTION_DOC_ID,TRANSACTION_DATE,REMARK,CATEGORY,PIECES,STOCK_ACTION,WAREHOUSE_ID,COMPANY_ID,ACCOUNT_YEAR,ACCOUNT_PERIOD_SEQNO,GLOB_JE_INDICATOR,GLMB_JE_INDICATOR,GLOB_JE_ID,GLMB_JE_ID,PPV_ROUTING_INDICATOR,POST_GL_INDICATOR,ACCOUNT_PERIOD_CODE,PLANT_ID,BEGINNING_ACCOUNT,SUM_BUSINESS_QTY,INNER_TRANSFER_DOC_ID,GENERATE_SOURCE,Attachments,ProcessInstanceId,UDF001,UDF002,UDF003,UDF011,UDF012,UDF013,UDF021,UDF022,UDF023,UDF024,UDF025,UDF026,UDF041,UDF042,UDF051,UDF052,UDF053,UDF054,PrintCount,EFNETStatus,EFNETAction,EFNETDOCCategory,EFNETDOCNumber,CreateDate,LastModifiedDate,ModifiedDate,CreateBy,LastModifiedBy,ModifiedBy,ApproveStatus,ApproveDate,ApproveBy,Owner_Org_RTK,Owner_Org_ROid,SOURCE_ID_RTK,SOURCE_ID_ROid,W056_AP_PPV_FLAG) ";
            //strSql += " SELECT '" + DH + "',CAST(GETDATE() AS DATE),'" + DB + "','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',NEWID(),CAST(GETDATE() AS DATE),'领料转费用',(SELECT CATEGORY FROM DOC WHERE DOC_ID='" + DB + "')," + tzsl + ",(SELECT STOCK_ACTION FROM DOC WHERE DOC_ID='" + DB + "'),'00000000-0000-0000-0000-000000000000',(SELECT TOP 1 COMPANY_ID FROM COMPANY),YEAR(DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "')),MONTH(DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "')),0,0,'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',0,1,MONTH(DATEADD(DAY,0,'" + dateTimePicker4.Value.ToShortDateString() + "')),'00000000-0000-0000-0000-000000000000',0," + tzsl + ",'00000000-0000-0000-0000-000000000000','0','','00000000-0000-0000-0000-000000000000',";
            //strSql += "0,0,0,0,0,0,'','','','','','','1900-01-01 00:00:00.0000000','1900-01-01 00:00:00.0000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',0,0,0,'','',GETDATE(),GETDATE(),GETDATE(),'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','N',GETDATE(),'00000000-0000-0000-0000-000000000000','PLANT','" + plantid + "','OTHER','00000000-0000-0000-0000-000000000000',0";

            //try
            //{
            //    if (db.ExecDataBySql1(strSql) > 0)
            //    {
            //    }
            //    else
            //    {

            //    }
            //}
            //catch
            //{

            //}

            for (int i = 0; i <= gridView2.RowCount - 1; i++)
            {
                if (Convert.ToDouble(gridView2.GetRowCellValue(i, "调整数量").ToString()) > 0.00)
                {
                    String ckbh = this.gridView2.GetRowCellValue(i, "仓库").ToString();
                    String clph = this.gridView2.GetRowCellValue(i, "品号").ToString();
                    String mo_id = this.gridView2.GetRowCellValue(i, "MOID").ToString();
                    String mo_d_id = this.gridView2.GetRowCellValue(i, "MODID").ToString();
                    String source_rtk = this.gridView2.GetRowCellValue(i, "SOURCEIDRTK").ToString();
                    String source_roid = this.gridView2.GetRowCellValue(i, "SOURCEIDROid").ToString();
                    String qty = this.gridView2.GetRowCellValue(i, "调整数量").ToString();
                    String qtycost = this.gridView2.GetRowCellValue(i, "调整金额").ToString();
                    String tzqbl = this.gridView2.GetRowCellValue(i, "调整比例").ToString();
                    String owner = this.gridView2.GetRowCellValue(i, "OwnerOrgROid").ToString();
                    String llid = this.gridView2.GetRowCellValue(i, "ISSUE_RECEIPT_D_ID").ToString();
                    String tzbl = (1 - Convert.ToDouble(tzqbl)).ToString();

                    strSql = "INSERT MO_TEMP_LTL1 SELECT '" + PropertyClass.OperatorCode + "','" + mo_id + "','" + mo_d_id + "','" + source_rtk + "','" + source_roid + "'," + qty + ",'','" + owner + "','" + ckbh + "'";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }
                    //更新领料单
                    strSql = "UPDATE ISSUE_RECEIPT_D SET ISSUE_RECEIPT_QTY=ISSUE_RECEIPT_QTY * " + tzbl + " ,ACTUAL_ISSUE_RECEIPT_QTY=ACTUAL_ISSUE_RECEIPT_QTY * " + tzbl + ",INVENTORY_QTY=INVENTORY_QTY * " + tzbl + ",ACTUAL_INVENTORY_QTY=ACTUAL_INVENTORY_QTY * " + tzbl + " WHERE ISSUE_RECEIPT_D_ID='" + llid + "'";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }

                    //更新库存交易明细表
                    strSql = "UPDATE TRANSACTION_LINE SET INVENTORY_QTY=INVENTORY_QTY * " + tzbl + ",COST_AMT=COST_AMT * " + tzbl + " WHERE SOURCE_ID_ROid='" + llid + "'";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }

                    //更新成本要素档
                    strSql = "UPDATE TRANSACTION_LINE SET COST_AMT=COST_AMT * " + tzbl + " WHERE EXISTS(SELECT * FROM TRANSACTION_LINE WHERE TRANSACTION_LINE.TRANSACTION_LINE_ID=TRANS_LINE_COST_DETAIL.TRANSACTION_LINE_ID AND TRANSACTION_LINE.SOURCE_ID_ROid='" + llid + "')";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }

                    //更新品号仓库档
                    strSql = "UPDATE ITEM_WAREHOUSE SET INVENTORY_QTY=B.INVENTORY_QTY,SECOND_QTY=B.SECOND_QTY FROM (SELECT A.ITEM_ID,A.WAREHOUSE_ID,SUM(INVENTORY_QTY*STOCK_ACTION) INVENTORY_QTY,SUM(SECOND_QTY*STOCK_ACTION) SECOND_QTY";
                    strSql += " FROM TRANSACTION_LINE A LEFT JOIN ITEM B ON A.ITEM_ID = B.ITEM_BUSINESS_ID LEFT JOIN WAREHOUSE C ON C.WAREHOUSE_ID = A.WAREHOUSE_ID WHERE B.ITEM_CODE = '" + clph + "' AND C.WAREHOUSE_CODE = '" + ckbh + "' GROUP BY A.ITEM_ID,A.WAREHOUSE_ID) B WHERE ITEM_WAREHOUSE.ITEM_ID = B.ITEM_ID AND ITEM_WAREHOUSE.WAREHOUSE_ID = B.WAREHOUSE_ID";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }

                    //更新品号批号（余额）档
                    strSql = "UPDATE ITEM_WAREHOUSE_BIN SET INVENTORY_QTY=B.INVENTORY_QTY,SECOND_QTY=B.SECOND_QTY FROM (SELECT A.ITEM_ID,A.WAREHOUSE_ID,A.ITEM_LOT_ID,A.BIN_ID,SUM(INVENTORY_QTY*STOCK_ACTION) INVENTORY_QTY,SUM(SECOND_QTY*STOCK_ACTION) SECOND_QTY ";
                    strSql += "FROM TRANSACTION_LINE A LEFT JOIN ITEM B ON A.ITEM_ID = B.ITEM_BUSINESS_ID LEFT JOIN WAREHOUSE C ON C.WAREHOUSE_ID = A.WAREHOUSE_ID WHERE B.ITEM_CODE = '" + clph + "' AND C.WAREHOUSE_CODE = '" + ckbh + "' GROUP BY A.ITEM_ID,A.WAREHOUSE_ID,A.ITEM_LOT_ID,A.BIN_ID) B WHERE ITEM_WAREHOUSE_BIN.ITEM_ID = B.ITEM_ID AND ITEM_WAREHOUSE_BIN.WAREHOUSE_ID = B.WAREHOUSE_ID AND ITEM_WAREHOUSE_BIN.ITEM_LOT_ID = B.ITEM_LOT_ID AND ITEM_WAREHOUSE_BIN.BIN_ID = B.BIN_ID";
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }

                    //更新工单
                    strSql = "UPDATE MO_D SET REQUIRED_QTY=REQUIRED_QTY - " + qty + " ,REPLACED_QTY=REPLACED_QTY - " + qty + ",ISSUED_QTY=ISSUED_QTY - " + qty + ",LEAST_REQUIRED_QTY=LEAST_REQUIRED_QTY - " + qty + ",ISSUED_REQ_QTY=ISSUED_REQ_QTY - " + qty + " WHERE MO_D_ID='" + mo_d_id + "'";
                   // textBox8.Text = strSql;
                    try
                    {
                        if (db.ExecDataBySql1(strSql) > 0)
                        {
                        }
                        else
                        {

                        }
                    }
                    catch
                    {

                    }
                }
            }

            //strSql = "EXEC [SP_MO_TEMP_LTL1] '" + PropertyClass.OperatorCode + "','" + textBox11.Tag + "','单身仓库','0','" + dateTimePicker1.Value.ToShortDateString() + "'";
            //try
            //{
            //    if (db.ExecDataBySql1(strSql) > 0)
            //    {

                    
            //    }
            //    else
            //    {
 
            //    }
            //}
            //catch
            //{

            //}

            //strSql = "EXEC [SP_TRAN_TEMP_LTL] '" + PropertyClass.OperatorCode + "','" + textBox14.Tag + "','单身仓库','0','" + dateTimePicker1.Value.ToShortDateString() + "'";
            //try
            //{
            //    if (db.ExecDataBySql1(strSql) > 0)
            //    {


            //    }
            //    else
            //    {

            //    }
            //}
            //catch
            //{

            //}

            MessageBox.Show("领/退料单调整成功！", "软件提示");
        }

        private void button12_Click(object sender, EventArgs e)
        {

            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Title = "Excel文件";
            ofd.FileName = "";
            ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
            ofd.Filter = "所有文件(*.*)|*.*|Excel2003文件(*.xls)|*.xls|Excel2007文件(*.xlsx)|*.xlsx";
            ofd.ValidateNames = true;
            ofd.CheckFileExists = true;
            ofd.CheckPathExists = true;
            string strName = string.Empty;
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                strName = ofd.FileName;
            }
            if (strName == "")
            {
                XtraMessageBox.Show("没有选择Excel文件！无法进行数据导入");
                return;
            }
            else
            {
                try
                {
                    DataSet myDs = new DataSet();
                    string text = string.Format("Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = '{0}';Extended Properties=Excel 8.0", strName);
                    string excelFirstTableName = GetExcelFirstTableName(strName);
                    myDs.Tables.Clear();
                    myDs.Clear();
                    this.gridControl3.DataSource = null;
                    OleDbConnection selectConnection = new OleDbConnection(text);
                    OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(string.Format("select * from [{0}]", excelFirstTableName), selectConnection);
                    oleDbDataAdapter.Fill(myDs);
                    this.gridControl3.DataSource = myDs.Tables[0];
                    this.gridView3.PopulateColumns();
                }
                catch (Exception ex)
                {
                    XtraMessageBox.Show("从电子表格文件中装载数据异常！", ex.Message);
                }
            }
        }

        public static string GetExcelFirstTableName(string excelFileName)
        {
            string tableName = null;
            if (File.Exists(excelFileName))
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
                  "OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + excelFileName))
                {
                    conn.Open();
                    DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    tableName = dt.Rows[0][2].ToString().Trim();
                }
            }
            return tableName;
        }

        private void button13_Click(object sender, EventArgs e)
        {
            String strSql = "";
            strSql = "INSERT INTO SIMULATE_COST_RESOURCE(Owner_Dept,Owner_Emp,SIMULATE_COST_RESOURCE_ID,ITEM_FEATURE_ID,ITEM_ID,CE1_CUR_LEVEL,CE2_CUR_LEVEL,CE3_CUR_LEVEL,CE4_CUR_LEVEL,CE5_CUR_LEVEL,CE6_CUR_LEVEL,CE7_CUR_LEVEL,CE8_CUR_LEVEL,CE9_CUR_LEVEL,CE10_CUR_LEVEL,CE11_CUR_LEVEL,CE12_CUR_LEVEL,CE13_CUR_LEVEL,CE14_CUR_LEVEL,CE15_CUR_LEVEL,CE16_CUR_LEVEL,CE17_CUR_LEVEL,CE18_CUR_LEVEL,CE19_CUR_LEVEL,CE20_CUR_LEVEL,REMARK,COMPANY_ID,UDF001,UDF002,UDF003,UDF011,UDF012,UDF013,UDF021,UDF022,UDF023,UDF024,UDF025,UDF026,UDF041,UDF042,UDF051,UDF052,UDF053,UDF054,ProcessInstanceId,CreateDate,LastModifiedDate,ModifiedDate,CreateBy,LastModifiedBy,ModifiedBy,ApproveStatus,ApproveDate,ApproveBy,Attachments,Owner_Org_RTK,Owner_Org_ROid)";
            strSql += "SELECT '00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',NEWID(),'00000000-0000-0000-0000-000000000000',ITEM_ID,(SELECT ISNULL(SUM(PIECE_PRICE), 0) FROM ITEM_ROUTING_D B WHERE A.ITEM_ROUTING_ID = B.ITEM_ROUTING_ID),0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,'','BD50CE83-C2DA-4799-9263-12140CEFBEF8',0,0,0,0,0,0,'','','','','','','1900-01-01 00:00:00.0000000','1900-01-01 00:00:00.0000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000',A.CreateDate,A.LastModifiedDate,A.ModifiedDate,A.CreateBy,A.LastModifiedBy,A.ModifiedBy,'Y',A.ApproveDate,A.ApproveBy,'',A.Owner_Org_RTK,A.Owner_Org_ROid";
            strSql += "FROM ITEM_ROUTING A WHERE NOT EXISTS(SELECT * FROM SIMULATE_COST_RESOURCE C WHERE C.ITEM_ID = A.ITEM_ID AND C.Owner_Org_ROid = A.Owner_Org_ROid)";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                }
                else
                {
                }
            }
            catch
            {

            }

            strSql = " UPDATE SIMULATE_COST_RESOURCE SET CE1_CUR_LEVEL=C.PIECE_PRICE FROM (SELECT A.ITEM_ID,A.Owner_Org_ROid,ISNULL(SUM(PIECE_PRICE),0) PIECE_PRICE FROM ITEM_ROUTING_D B LEFT JOIN ITEM_ROUTING A ON A.ITEM_ROUTING_ID=B.ITEM_ROUTING_ID GROUP BY A.ITEM_ID,A.Owner_Org_ROid) C WHERE SIMULATE_COST_RESOURCE.ITEM_ID=C.ITEM_ID AND SIMULATE_COST_RESOURCE.Owner_Org_ROid=C.Owner_Org_ROid";
            try
            {
                if (db.ExecDataBySql1(strSql) > 0)
                {
                    MessageBox.Show("更新成功！","系统提示");
                }
                else
                {
                }
            }
            catch
            {

            }

        }
    }
}
        

    

